package ca.ualberta.cs.CLRDB.conference;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class RolesManagement extends EntityRelManagement{

	public RolesManagement() {
		mainTableName = "Roles";
	}
	
	@Override
	public void initialize(Statement statement) throws SQLException {
		try{
			statement.executeUpdate("DROP TABLE Roles");
			statement.executeUpdate("DROP TABLE PeopleRoles");
		}catch(Exception e){}
		
		//all CHAR(1)s should get 1 or 0
		statement.executeUpdate("CREATE TABLE Roles ( " +
				"role VARCHAR(10) NOT NULL, " +
				"seeAllPapers CHAR(1), " +
				"seeAllReviews CHAR(1), " + //see all annotations and comments
				"seeAllReviewers CHAR(1), " + //see all reviewer profiles
				//"seeAssignedPapers CHAR(1), " + //
				"seeRelatedReviews CHAR(1), " + //see reviews of all reviewers on the assigned papers
				"seeRelatedReviewers CHAR(1), " + //author: see reviewers' profiles, reviewers: see related reviewers' profiles
				"canUploadPaper CHAR(1), " +
				"canAssignPaper CHAR(1), " +
				"seeAuthors CHAR(1), " +
				"CONSTRAINT pk_role PRIMARY KEY(role), " +
				"CONSTRAINT role_vals CHECK (role IN ('reviewer','pcchair','author')) " +
		") IN CLRSPACE"); 
		
		numOfCols = 9; 
		
		setupDefaultRoleProperties(statement);
		
		statement.executeUpdate("CREATE TABLE PeopleRoles ( " +				
				"personId INTEGER NOT NULL, " +				
				"role VARCHAR(10) NOT NULL, " +
				"CONSTRAINT pk_user_role PRIMARY KEY(personId,role), " +
				//"CONSTRAINT role_vals CHECK (role IN ('reviewer','admin','author')), " +
				"CONSTRAINT FK_personId FOREIGN KEY (personId) " +
            		"REFERENCES Person (ID) ON DELETE CASCADE, " +				
            	"CONSTRAINT FK_role FOREIGN KEY (role) " +
            		"REFERENCES Roles (role) ON DELETE CASCADE " +
				") IN CLRSPACE"); 
		
		//I preferred to use (pesudousername,username) as the FK->person, because it is easier to have username
		//it is difficult to maintain both username and pseudousername, but it should only be done once: when roles are taken
		//but, db2 didn't allow me to do so, because username is not unique in Person, while I tried to use combination
		//with pesudousername which is a unique index on them.. still it didn't allow me and I have to use ID
		//numOfCols = 2;				
	}
	
	private void setupDefaultRoleProperties(Statement statement){
		
		String keys = "role, seeAllPapers, seeAllReviews, seeAllReviewers, " +
				"seeRelatedReviews, seeRelatedReviewers, canUploadPaper, canAssignPaper," +
				"seeAuthors";
		
		try{
			statement.executeUpdate("INSERT INTO Roles ("+keys+") VALUES (" +
					"'author',   '0', '0', '0', '1', '1', '1', '0', '0'"+
					")");
			statement.executeUpdate("INSERT INTO Roles ("+keys+") VALUES (" +
					"'reviewer', '0', '0', '0', '1', '0', '0', '0', '1'"+
					")");
			statement.executeUpdate("INSERT INTO Roles ("+keys+") VALUES (" +
					"'pcchair',    '1', '1', '1', '0', '0', '0', '1', '1'"+
					")");
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	public void setupRoleProperties(Statement statement){
		
	}
	
	public static boolean userHasRole(String username, Statement statement){
		boolean has = false;
		try{
			ResultSet res = statement.executeQuery("SELECT * FROM PeopleRoles, Person WHERE ID = personId " +
					"AND username = '"+username+"' " +
					"AND role = '"+role+"'");
			
			if(res.next())
				return true;
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		return has;
	}

}
